#!/usr/bin/python
# -*- coding: utf-8 -*-  
"""
@Project : hello 
@file : batch_update.py
@Author : shenj
@time : 2025/5/14 17:31
@func :
"""

import oracledb
import logging
from datetime import datetime
from typing import List, Tuple

# 配置日志
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s',
    handlers=[logging.StreamHandler()]
)
logger = logging.getLogger(__name__)

# 数据库配置
DB_CONFIG = {
    "user": "your_username",
    "password": "your_password",
    "dsn": "your_host:1521/your_service",
    "min": 5,
    "max": 20
}


class BatchSQLExecutor:
    def __init__(self):
        self.pool = None
        self.batch_size = 1000  # 每批处理1000条记录

    def connect(self):
        """创建数据库连接池"""
        try:
            self.pool = oracledb.create_pool(**DB_CONFIG)
            logger.info("数据库连接池创建成功")
        except Exception as e:
            logger.error(f"创建连接池失败: {e}")
            raise

    def close(self):
        """关闭连接池"""
        if self.pool:
            self.pool.close()
            logger.info("数据库连接池已关闭")

    def execute_batch_update(self, sql: str, ids: List[str], description: str):
        """批量执行更新操作"""
        start_time = datetime.now()
        total = len(ids)
        processed = 0

        logger.info(f"开始执行 {description}，共 {total} 条记录")

        try:
            with self.pool.acquire() as conn:
                with conn.cursor() as cursor:
                    # 分批处理
                    for i in range(0, total, self.batch_size):
                        batch = ids[i:i + self.batch_size]
                        params = [(id,) for id in batch]

                        try:
                            cursor.executemany(sql, params)
                            conn.commit()
                            processed += len(batch)

                            # 打印进度
                            if processed % (self.batch_size * 10) == 0 or processed == total:
                                progress = processed / total * 100
                                logger.info(f"进度: {processed}/{total} ({progress:.1f}%)")

                        except Exception as e:
                            conn.rollback()
                            logger.error(f"处理批次 {i // self.batch_size + 1} 失败: {e}")
                            raise

        except Exception as e:
            logger.error(f"{description} 执行失败: {e}")
            raise

        elapsed = (datetime.now() - start_time).total_seconds()
        logger.info(f"{description} 完成，共处理 {processed} 条记录，耗时 {elapsed:.2f} 秒")

    def execute_step3(self, ord_item_xm_ids: List[str]):
        """执行步骤3的查询和更新"""
        start_time = datetime.now()
        total = len(ord_item_xm_ids)
        processed = 0

        logger.info(f"开始执行步骤3，共 {total} 条ORD_ITM_XM_ID")

        try:
            with self.pool.acquire() as conn:
                with conn.cursor() as cursor:
                    # 分批查询ORDER_ID
                    order_ids = set()

                    for i in range(0, total, self.batch_size):
                        batch = ord_item_xm_ids[i:i + self.batch_size]

                        # 构建IN子句参数
                        in_clause = ",".join([":" + str(j + 1) for j in range(len(batch))])
                        query_sql = f"""
                        SELECT co.ORDER_ID FROM 
                        CUSTOMER_ORDER_ITEM_XM coix
                        JOIN CUSTOMER_ORDER_ITEM coi ON coi.ORDER_ITEM_ID = coix.PAR_ORD_ITM_XM_ID 
                            AND coi.MEMBER_ID = coix.MEMBER_ID
                        JOIN CUSTOMER_ORDER co ON co.ORDER_ID = coi.ORDER_ID 
                            AND coi.MEMBER_ID = co.LOY_MEMBER_ID
                        WHERE coix.ORD_ITM_XM_ID IN ({in_clause})
                        """

                        try:
                            cursor.execute(query_sql, batch)
                            batch_order_ids = [row[0] for row in cursor]
                            order_ids.update(batch_order_ids)
                            processed += len(batch)

                            # 打印进度
                            if processed % (self.batch_size * 10) == 0 or processed == total:
                                progress = processed / total * 100
                                logger.info(f"查询进度: {processed}/{total} ({progress:.1f}%)")

                        except Exception as e:
                            logger.error(f"查询批次 {i // self.batch_size + 1} 失败: {e}")
                            raise

                    # 批量更新ORDER表
                    if order_ids:
                        order_ids = list(order_ids)
                        update_total = len(order_ids)
                        update_processed = 0

                        logger.info(f"找到 {update_total} 个需要更新的ORDER_ID")

                        update_sql = """
                        UPDATE CUSTOMER_ORDER SET LAST_UPADTED = UTC_TIMESTAMP
                        WHERE ORDER_ID = :1
                        """

                        for i in range(0, update_total, self.batch_size):
                            batch = order_ids[i:i + self.batch_size]
                            params = [(id,) for id in batch]

                            try:
                                cursor.executemany(update_sql, params)
                                conn.commit()
                                update_processed += len(batch)

                                # 打印进度
                                if update_processed % (self.batch_size * 10) == 0 or update_processed == update_total:
                                    progress = update_processed / update_total * 100
                                    logger.info(f"更新进度: {update_processed}/{update_total} ({progress:.1f}%)")

                            except Exception as e:
                                conn.rollback()
                                logger.error(f"更新批次 {i // self.batch_size + 1} 失败: {e}")
                                raise

        except Exception as e:
            logger.error(f"步骤3执行失败: {e}")
            raise

        elapsed = (datetime.now() - start_time).total_seconds()
        logger.info(f"步骤3完成，共处理 {total} 条ORD_ITM_XM_ID，更新 {len(order_ids)} 条ORDER记录，耗时 {elapsed:.2f} 秒")


def main():
    # 示例数据 - 实际使用时替换为您的数据
    loy_rdm_itm_ids = ["id1", "id2", "id3", ...]  # 约5万条
    loy_acrl_itm_ids = ["id1", "id2", "id3", ...]  # 约5万条
    ord_item_xm_ids = ["id1", "id2", "id3", ...]  # 约5万条

    executor = BatchSQLExecutor()

    try:
        # 连接数据库
        executor.connect()

        # 执行步骤1
        step1_sql = """
        UPDATE CUSTOMER_LOY_RDM_ITM SET LAST_UPADTED = UTC_TIMESTAMP
        WHERE LOY_RDM_ITM_ID = :1
        """
        executor.execute_batch_update(step1_sql, loy_rdm_itm_ids, "步骤1 - 更新CUSTOMER_LOY_RDM_ITM")

        # 执行步骤2
        step2_sql = """
        UPDATE CUSTOMER_LOY_ACRL_ITM SET LAST_UPADTED = UTC_TIMESTAMP
        WHERE LOY_ACRL_ITM_ID = :1
        """
        executor.execute_batch_update(step2_sql, loy_acrl_itm_ids, "步骤2 - 更新CUSTOMER_LOY_ACRL_ITM")

        # 执行步骤3
        executor.execute_step3(ord_item_xm_ids)

    except Exception as e:
        logger.error(f"主程序执行失败: {e}")
    finally:
        executor.close()


if __name__ == "__main__":
    main()